package org.streets.database;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicLong;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.streets.commons.util.ClassUtils;
import org.streets.commons.util.StringUtils;
import org.streets.database.annotations.SQLMapping;
import org.streets.database.annotations.IgnoreUpdate;
import org.streets.database.annotations.NestedFetch;
import org.streets.database.dialect.Dialect;

/**
 * 名字参数SQL解析功能类
 * 数据分页查询sql生成等
 * 
 * @author dzb
 *
 */
public final class SQLUtils {

	private static final Pattern PatternFrom = Pattern.compile("from(.*?)\\s+(where)",
			Pattern.CASE_INSENSITIVE);

	private static final Pattern PatternWhere = Pattern.compile("select.*?from(.*?)\\s+(where)",
			Pattern.CASE_INSENSITIVE);

	private static final Pattern PatternOrder = Pattern.compile("select.*?from(.*?)\\s+(order\\s+by)",
			Pattern.CASE_INSENSITIVE);
	/**
	 * Cache for table name
	 */
	private static final Map<Class<?>, String> TableNameCaches = new Hashtable<Class<?>, String>();
	/**
	 * Cache for JPA Entity Insert SQL
	 */
	private static final Map<Class<?>, String> C_SQLCaches = new Hashtable<Class<?>, String>();
	/**
	 * Cache for JPA Entity Retrive SQL
	 */
	private static final Map<Class<?>, String> R_SQLCaches = new Hashtable<Class<?>, String>();
	/**
	 * Cache for JPA Entity Update SQL
	 */
	private static final Map<Class<?>, String> U_SQLCaches = new Hashtable<Class<?>, String>();
	/**
	 * Cache for JPA Entity Delete SQL
	 */
	private static final Map<Class<?>, String> D_SQLCaches = new Hashtable<Class<?>, String>();
	
	private static final AtomicLong UID_GENERATOR = new AtomicLong(System.currentTimeMillis());

    public static String nextUID() {
    	return Long.toHexString(UID_GENERATOR.getAndIncrement());
    }
	/**
	 * 
	 * @param paramSql
	 * @return
	 */
	public static String parsePreparedSQL(String paramSql) {
		return paramSql.replaceAll("(\\:[a-zA-Z0-9\\_]+(\\.[a-zA-Z\\_]{1}[a-zA-Z0-9\\_]*)?)", "?");
	}
	/**
	 * 从带参数的sql中获取到参数名字
	 * @param sql
	 * @return
	 */
	public static String[] parseParameterNames(String sql) {
		List<String> list = new ArrayList<String>();
		int fromIndex = 0;
		for (;;) {
			int pos = sql.indexOf(':', fromIndex);
			if (pos == (-1)) {
				break;
			}
			//String name = getParameterName(sqlQuery, pos);
			StringBuilder sb = new StringBuilder(10);
			for (int i = pos + 1; i < sql.length(); i++) {
				char ch = sql.charAt(i);
				if (((ch >= '0' && ch <= '9') || (ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z'))
						|| ch == '.' || ch == '_') {
					sb.append(ch);
				} else {
					break;
				}
			}
			
			if (sb.length() == 0)
				throw new SQLNestedException("Bad parameter ':' declaration in SQL: " + sql + ", position " + pos);

			String name = sb.toString();
			
			if (!name.matches("[0-9a-zA-Z\\_]+(\\.[a-zA-Z\\_]{1}[a-zA-Z0-9\\_]*)?"))
				throw new SQLNestedException("Bad parameter ':" + name + "' declaration in SQL: " + sql + ", position " + pos);

			fromIndex = pos + 1 + sb.length();
			list.add(name);
		}		
		return list.toArray(new String[0]);
	}
	
	/**
	 * 
	 * @param typeExp format : name1:alias1, name2:alias2 或着 name1:11 , name2:23 
	 */
	public static int[] parseJDBCTypes(String[] names, String paramTypeExpr) {
		
		assert names != null && paramTypeExpr != null : "Cannot be null";
		
		if (paramTypeExpr.charAt(0) == '[' 
			&& paramTypeExpr.charAt(paramTypeExpr.length()-1) == ']') {
			paramTypeExpr = paramTypeExpr.substring(1, paramTypeExpr.length()-1);
		}				
		
		String[] types = paramTypeExpr.split("\\s*[,;]\\s*");
		
		if (types.length < names.length) {
			throw new SQLNestedException("Illigal type count. types count must bigger than  ["+names.length+"]");
		}
		
		Map<String, Integer> tempTypes = new HashMap<String, Integer>();
		
		for (String t : types) {
			String[] nt = t.split("\\s*:\\s*");
			if (nt.length != 2) {
				throw new SQLNestedException("Illigal type expresstion ["+paramTypeExpr+"].check it please.");
			}
			if (StringUtils.isNumeric(nt[1])) {
				tempTypes.put(nt[0], new Integer(nt[1]));
			} else {
				int iT = SQLTypeMapping.getSQLTypeByAlias(nt[1]);
				if (iT == SQLTypeMapping.UNKNOWN_TYPE) {
					throw new SQLNestedException("Illigal type alias["+nt[1]+"] of params["+nt[0]+"]. can not build jdbc type array");
				}
				tempTypes.put(nt[0], new Integer(iT));
			}			
		}
		
		int[] result = new int[names.length];
		
		for (int i = 0; i<names.length; ++i) {
			Integer it = tempTypes.get(names[i]); 
			if (it == null) {
				throw new SQLNestedException("Param["+names[i]+"] has no type value defined. can not build type array");
			}
			result[i] = it.intValue();
		}
		// clear temp map
		tempTypes.clear();
		// return type array
		return result;
	}

	/**
	 * Convert a Map of parameter types to a corresponding int array.
	 * This is necessary in order to reuse existing methods on JdbcTemplate.
	 * Any named parameter types are placed in the correct position in the
	 * Object array based on the parsed SQL statement info.
	 * @param npSql the parsed SQL statement
	 * @param paramSource the source for named parameters
	 */
	public static int[] parseJDBCTypes(String[] names, Map<String, String> types) {
		
		assert names != null && types.size() >= names.length : "Types's count["+types.size()+"] must greater than named param count["+names.length+"]"; 
		
		int[] result = new int[names.length];
		
		for (int i = 0; i < names.length; i++) {
			String t = types.get(names[i]);
			if (t == null) {
				//sqlTypes[i] = 
				throw new SQLNestedException("Illigal type value on ["+names[i]+"]");
			}			
			if (StringUtils.isNumeric(t))
				result[i] = Integer.parseInt(t);
			else {
				int iT = SQLTypeMapping.getSQLTypeByAlias(t);
				if (iT == SQLTypeMapping.UNKNOWN_TYPE) {
					throw new SQLNestedException("Illigal type alias["+t+"] of param["+names[i]+"]. can not build jdbc type array");
				}
				result[i] = iT;					
			}
		}
		// Do not clear any thing here
		return result;
	}	
	
	@SuppressWarnings("unchecked")
	public static Object[] buildNamedParamValues(String[] names, PV[] values) {
		
		if (names == null || values == null || names.length == 0 || values.length == 0) {
			return null;
		}
		
		if (names.length > values.length) {
			throw new SQLNestedException("Param names can not bigger than param values");
		}
		
		Object[] result = new Object[names.length];
		Map<String, Object> tempMap = new HashMap<String, Object>();
		for (PV nv : values) {
			//处理Collection形式的参数
			if (! (nv.value instanceof Collection)) {
				tempMap.put(nv.name, nv.value);
			} else {
				StringBuilder sb = new StringBuilder();
				for (Iterator iter = ((Collection) nv.value).iterator(); iter.hasNext();) {
					Object o = iter.next();
					sb.append(o!=null?o.toString():"");
					sb.append(",");
				}
				if (sb.length() > 0){
					sb.deleteCharAt(sb.length()-1);
				}
				nv.value = sb.toString();
			}
		}
		for (int i = 0; i < names.length; i++) {
			result[i] = tempMap.get(names[i]);
		}
		tempMap.clear();
		return result;
	}
	
	public static Object[] buildDialectParamBindValues(Object[] values, Limits limits, Dialect dialect) {
		
        Long[] limit_values = buildDialectLimitValues(limits, dialect);        
        
        if (limit_values == null) {
        	return values;
        }
        // for copy logic
        if (values == null) {
        	values = new Object[0];
        }
        Object[] result = new Object[values.length+limit_values.length];
		if (dialect.bindLimitParametersFirst()) {
			System.arraycopy(limit_values, 0, result, 0, limit_values.length);
			System.arraycopy(values, 0, result, limit_values.length, values.length);
		} else {
			System.arraycopy(values, 0, result, 0, values.length);
			System.arraycopy(limit_values, 0, result, values.length, limit_values.length);			
		}
		return result;		
	}
	/**
	 * 将查询限制（分页）值build到实际的参数中去
	 * @param names
	 * @param values
	 * @param limits
	 * @param dialect
	 * @return
	 */
	public static Object[] buildDialectParamBindValues(String[] names, PV[] values, Limits limits, Dialect dialect) {		
        Object[] param_values = buildNamedParamValues(names, values);    	
        return buildDialectParamBindValues(param_values, limits, dialect);        
	}
	/**
	 * 创建limits查询语句
	 * @param paramSql
	 * @param limits
	 * @param dialect
	 * @return
	 */
	public static String buildDialectLimitQuery(String sql, Limits limits, Dialect dialect) {
        if (limits.getOffset() <=0 && limits.getLimit() <= 0)
            return sql;
        return dialect.getLimitSQL(sql, limits.getOffset()>0, limits.getLimit()>0);
	}		
	/**
	 * 创建经过顺序转换的limit值数组
	 * @param limits
	 * @param dialect
	 * @return
	 */
	public static Long[] buildDialectLimitValues(Limits limits, Dialect dialect) {
		Long[] limit_values = null;
        if (limits.getOffset()>0 && limits.getLimit()>0) {
            boolean switchLimitParams = dialect.bindLimitParametersInReverseOrder();
            limit_values = new Long[2];
            limit_values[0] = switchLimitParams ? (limits.getLimit()+limits.getOffset()) : limits.getOffset();
            limit_values[1] = switchLimitParams ? limits.getOffset() : (limits.getOffset()+limits.getLimit());
        }
        else if (limits.getOffset()>0) {
            limit_values = new Long[1];
            limit_values[0] = limits.getOffset();
        }
        else if (limits.getLimit()>0) {
            limit_values = new Long[1];
            limit_values[0] = Long.valueOf(limits.getLimit());
        }
        return limit_values;
	}

	/**
	 * Resolve table name from jpa annotations
	 * @param <T>
	 * @param clazz
	 * @return
	 */
	public static <T> String resolveTableName(Class<T> clazz) {
		
		String t_n = TableNameCaches.get(clazz);
		
		if (t_n != null) return t_n;
		
		Entity entity = clazz.getAnnotation(Entity.class);
		if (entity == null) {
			throw new SQLNestedException("Class ["+clazz+"] is not a entity");
		}
		t_n = entity.name();
		if (StringUtils.isEmpty(t_n)) {
			Table table = clazz.getAnnotation(Table.class);
			t_n = table.name();
		}
		if (StringUtils.isEmpty(t_n)) {
			throw new SQLNestedException("Entity ["+clazz+"] has no tablename defined.");
		}
		TableNameCaches.put(clazz, t_n);
		return t_n;
	}
	/**
	 * 获取Field对应的Column
	 * @param <T>
	 * @param field
	 * @return
	 */
	public static <T> String resolveColumnName(Field field) {
		
		String c_n = null;
		
		Column column = field.getAnnotation(Column.class);
		
		if (column != null) {
			c_n = column.name();
		} 
				
		if (StringUtils.isEmpty(c_n)) {
			c_n = SQLUtils.convPropertyNameToColumnName(field.getName());
		}
		
		return c_n;
	}
	/**
	 * 
	 * @param <T>
	 * @param bean
	 * @param params
	 * @return
	 */
	public static <T> String buildJPAInsertSQL(Class<T> clazz) {
		
		String result = C_SQLCaches.get(clazz);
		
		if (result != null) return result;

		// check defined SQLDef
		SQLMapping def = clazz.getAnnotation(SQLMapping.class);
		if (def != null && StringUtils.isNotEmpty(def.insert())) {
			C_SQLCaches.put(clazz, def.insert());
			return def.insert();
		}
		
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();			
		
		sb1.append("insert into ").append(resolveTableName(clazz)).append(" ( ");
		sb2.append(" values ( ");
							
		Field[] fields = ClassUtils.getDeclaredFields(clazz);
		
		for (Field field : fields) {
			
			if (field.getAnnotations().length == 0) {
				continue;
			}
			//如果是忽略的就跳过
			if (field.getAnnotation(IgnoreUpdate.class) != null) {
				continue;
			}
			//如果是Nested忽略的就跳过
			if (field.getAnnotation(NestedFetch.class) != null) {
				continue;
			}
			
			String c_n = resolveColumnName(field);
			sb1.append(c_n).append(", ");
			sb2.append(":").append(field.getName()).append(", ");							
		}
				
		sb1.deleteCharAt(sb1.length()-2);
		sb1.append(") ");
		sb2.deleteCharAt(sb2.length()-2);
		sb2.append(")");
		
		result =sb1.append(sb2).toString();
		
		C_SQLCaches.put(clazz, result);
		
		return result;
	}
	
	/**
	 * 
	 * @param <T>
	 * @param bean
	 * @param params
	 * @return
	 */
	public static <T> String buildJPAUpdateSQL(Class<T> clazz) {
		
		String result = U_SQLCaches.get(clazz); 
		
		if (result != null) return result;

		// check defined SQLDef
		SQLMapping def = clazz.getAnnotation(SQLMapping.class);
		if (def != null && StringUtils.isNotEmpty(def.update())) {
			U_SQLCaches.put(clazz, def.update());
			return def.update();
		}

		StringBuilder sb = new StringBuilder();
			
		sb.append("update ").append(resolveTableName(clazz)).append(" set ");
	
		String id_column = null;
		String id_field = null;
		
		
		Field[] fields = ClassUtils.getDeclaredFields(clazz);
		
		for (Field field : fields) {
			
			if (field.getAnnotations().length == 0) {
				continue;
			}
			
			if (field.getAnnotation(IgnoreUpdate.class) != null) { 
				continue;
			}
			//如果是Nested忽略的就跳过
			if (field.getAnnotation(NestedFetch.class) != null) {
				continue;
			}
						
			String c_n = resolveColumnName(field);
						
			if (id_column ==  null && field.getAnnotation(Id.class) != null) {
				id_column = c_n;
				id_field = field.getName();
				continue;
			}
			
			sb.append(c_n).append(" = :").append(field.getName()).append(", ");
		}
		
		sb.deleteCharAt(sb.length()-2);		
		sb.append(" where ")
			.append(id_column).append(" = :").append(id_field);			
		
		result = sb.toString();		
		
		U_SQLCaches.put(clazz, result);
		
		return result;
	}
	/**
	 * 
	 * @param <T>
	 * @param clazz
	 * @return
	 */
	public static <T> String buildJPARetriveSQL(Class<T> clazz) {
		
		String result = R_SQLCaches.get(clazz);
		
		if (result != null) return result;
		
		// check defined SQLDef
		SQLMapping def = clazz.getAnnotation(SQLMapping.class);
		if (def != null && StringUtils.isNotEmpty(def.select())) {
			R_SQLCaches.put(clazz, def.select());
			return def.select();
		}
		
		StringBuilder sb1 = new StringBuilder();
				
		sb1.append("select * from ");
		sb1.append(resolveTableName(clazz));
		sb1.append(" where ");
		
		String id_column = null;
		String id_field = null;
		
		Field[] fields = ClassUtils.getDeclaredFields(clazz);
		
		for (Field field : fields) {
			if (field.getAnnotation(Id.class) != null) {
				id_field = field.getName();
				id_column = resolveColumnName(field);
				break;
			}
		}
		sb1.append(" trim(");
		sb1.append(id_column).append(") = :").append(id_field);
		
		result = sb1.toString();		
		
		R_SQLCaches.put(clazz, result);
		
		return result;

	}

	/**
	 * 
	 * @param <T>
	 * @param bean
	 * @return
	 */
	public static <T> String buildJPADeleteSQL(Class<T> clazz) {
		
		String result = D_SQLCaches.get(clazz);
		
		if (result != null) return result;
		
		// check defined SQLDef
		SQLMapping def = clazz.getAnnotation(SQLMapping.class);
		if (def != null && StringUtils.isNotEmpty(def.delete())) {
			D_SQLCaches.put(clazz, def.delete());
			return def.delete();
		}
				
		StringBuilder sb1 = new StringBuilder();
				
		sb1.append("delete from ");
		sb1.append(resolveTableName(clazz));
		sb1.append(" where ");
		
		String id_column = null;
		String id_field = null;
		
		Field[] fields = ClassUtils.getDeclaredFields(clazz);
			
		for (Field field : fields) {
			if (field.getAnnotation(Id.class) != null) {
				id_field = field.getName();
				id_column = resolveColumnName(field);
				break;
			}
		}
		sb1.append(" trim(");
		result = sb1.append(id_column).append(") = :").append(id_field).toString();
		
		D_SQLCaches.put(clazz, result);
		
		return result;
	}
	
	
	public static <T> Object[] buildJPAFieldValues(String[] fieldNames, T bean) 
		throws SecurityException, NoSuchFieldException, IllegalArgumentException, 
		IllegalAccessException, NoSuchMethodException, InvocationTargetException {
		
		Class<?> clazz = bean.getClass();
		
		List<Object> params = new ArrayList<Object>();
		
		for (String f_n : fieldNames) {
			
			Field field = ClassUtils.getDeclaredField(clazz, f_n);			
			Object pv;
			
			if ((field.getModifiers() | Modifier.PUBLIC) == Modifier.PUBLIC) {				
				pv = field.get(bean);
			} else {
				Class<?> f_t = field.getClass();
				Method method;
				if (boolean.class.equals(f_t) || Boolean.class.equals(f_t)) {
					method = clazz.getMethod("is"+StringUtils.capitalize(field.getName()));
				} else {
					method = clazz.getMethod("get"+StringUtils.capitalize(field.getName()));
				}
				pv = method.invoke(bean);
			}
			params.add(pv);
		}
		// Get the orderable params
		return params.toArray();	
	}
	/**
	 * 将SQL中涉及到得每个表都加上制定的条件
	 * @param sql
	 * @param field
	 * @param criteria
	 * @return
	 */
	public static String buildCriteriaToAll(String sql, String field, String criteria) {
		
		sql = sql.trim();		
		String s = sql.toLowerCase();

		boolean startWithSelect = s.startsWith("select");

		if (!startWithSelect) {
			String alias = "";
			int w = s.indexOf("where");
			int o = s.indexOf("order");
			if (w >0) {
				Matcher m = PatternFrom.matcher(sql);
				if (m.find()) {
					alias = m.group(1).trim();
				}
			}

			if (w <0 && o > 0) {
				alias = sql.substring(4, o);
			}
			else {
				alias = sql.substring(4);
			}
			String[] as = alias.split("[\\s]+");
			alias = as[as.length-1];
			sql = "select "+alias+" "+sql;
		}

		Matcher matcher = PatternWhere.matcher(sql);
		StringBuilder result = new StringBuilder();
		int pos = 0;
		
		int round=0;
		
		while (matcher.find()) {
			round++;
			result.append(sql.substring(pos, matcher.end(2)+1));
			pos = matcher.end(2)+1;
			//StringBuilder sb = new StringBuilder();
			String froms = matcher.group(1);
			froms = convFromBlockToAlias(froms, field, criteria);
			result.append(froms);
			result.append(" and ");
		}

		if (round==0) {
			matcher = PatternOrder.matcher(sql);
			if (matcher.find()) {
				round++;
				result.append(sql.substring(pos, matcher.start(2)));
				pos = matcher.start(2);
				//StringBuilder sb = new StringBuilder();
				String froms = matcher.group(1);
				froms = convFromBlockToAlias(froms, field, criteria);
				result.append("where ").append(froms).append(" ");
			}
		}

		if (round==0) {
			// process from
			pos = sql.indexOf("from");
			if (pos > 0) {
				String froms = sql.substring(pos+4);
				if (StringUtils.isNotBlank(froms)) {
					froms = convFromBlockToAlias(froms, field, criteria);
				}
				result.append(sql).append(" where ").append(froms);
			}
			//skip all.
			pos = sql.length();
		}

		result.append(sql.substring(pos));
		
		return result.toString();
	}

	/**
	 * 
	 * @param from
	 * @param propName
	 * @param criteria
	 * @return
	 */
	private static String convFromBlockToAlias(String from, String propName, String criteria) {
		StringBuilder sb = new StringBuilder();
		String[] fromArray = StringUtils.split(from, ",");
		for (int i=0; i<fromArray.length; i++) {
			String[] as = fromArray[i].trim().split("[\\s]+");
			String alias = as[0];

			if (as.length > 1) {
				alias = as[as.length-1];
			}
			if (i>0) {
				sb.append(" and ");
			}
			sb.append(alias).append(".").append(propName).append(criteria);
		}
		return sb.toString();
	}
	

//	/**
//	 * Convert <code>String</code>s in table name format (uppercase, underline seperating words)
//	 * into entity name format (camel-cased). For example, <code>X_DOM_TABLE</code> is
//	 * converted into <code>XDomTable</code>
//	 *
//	 * @param tableName
//	 * @return
//	 */
//	public static String convTableNameToClassName(String tableName, String tablePrefix) {
//		assert tableName != null : "'tableName' cannot be null.";
//		tableName = ClassUtils.getShortClassName(tableName);
//		String name = TableNameCaches.get(tableName);
//
//		if (StringUtils.isBlank(name)) {
//			StringBuilder sb = new StringBuilder();
//			tableName = tableName.toLowerCase();
//			if (tablePrefix != null) {
//				tablePrefix = tablePrefix.toLowerCase();
//				tableName = tableName.replaceFirst(tablePrefix, "");
//			}			
//			String[] segs = StringUtils.split(tableName, '_');
//			for (String seg : segs) {
//				if (tablePrefix != null) {
//					tablePrefix = tablePrefix.toLowerCase();
//				}
//				if (seg.trim().length() > 0) {
//					sb.append(StringUtils.capitalize(seg.toLowerCase()));
//				}
//			}
//			name = sb.toString();
//			if (TableNameCaches.size() > 10) {
//				TableNameCaches.clear();
//			}
//			TableNameCaches.put(tableName, name);
//		}
//		return name;
//	}
//	/**
//	 *
//	 * @param clazzName
//	 * @return
//	 */
//	public static String convClassNameToTableName(String clazzName, String tablePrefix) {
//		assert clazzName != null : "'modelName' cannot be null.";
//		clazzName = ClassUtils.getShortClassName(clazzName);
//		String name = TableNameCaches.get(clazzName);
//		if (StringUtils.isNotBlank(name)) return name;
//
//		int pos = 0;
//
//		StringBuilder sb = new StringBuilder();
//		for (int i=0; i<clazzName.length(); i++) {
//			if (Character.isUpperCase(clazzName.charAt(i))) {
//				sb.append(clazzName.substring(pos, i));
//				if (sb.length()>0) {
//					sb.append('_');
//					pos = i;
//				}
//			}
//		}
//		if (pos > 0) {
//			name = sb.append(clazzName.substring(pos)).toString().toUpperCase();
//		} else {
//			name = clazzName.toUpperCase();
//		}
//		if (tablePrefix != null) {
//			tablePrefix = tablePrefix.trim();
//			if (tablePrefix.length() > 0) {
//				name = tablePrefix.toUpperCase() + "_"+ name;
//			}			 
//		}
//		if (TableNameCaches.size() > 10) {
//			TableNameCaches.clear();
//		}
//		TableNameCaches.put(clazzName, name);
//		return name;
//	}
	
	/**
	 *
	 * @param columnName
	 * @return
	 */
	public static String convColumnNameToPropertyName(String columnName) {
		
		assert columnName != null : "'fieldName' cannot be null.";

		StringBuilder sb = new StringBuilder();
		String[] segs = StringUtils.split(columnName, '_');
		for (String seg : segs) {
			sb.append(StringUtils.capitalize(seg));
		}
		
		return StringUtils.uncapitalize(sb.toString());
	}
	/**
	 *
	 * @param propName
	 * @return
	 */
	public static String convPropertyNameToColumnName(String propName) {
		assert propName != null : "'propName' cannot be null.";

		StringBuilder field = new StringBuilder();
		String alias = "";
		int p = propName.indexOf(".");
		if (p>0) {
			alias = propName.substring(0, p);
			propName = propName.substring(p+1);
		}
		int pos = 0;
		for (int i=0; i<propName.length(); i++) {
			if (Character.isUpperCase(propName.charAt(i))) {
				field.append(StringUtils.uncapitalize(propName.substring(pos, i)));
				field.append('_');
				pos = i;
			}
		}
		String columnName = field.append(propName.substring(pos)).toString().toLowerCase();
		if (alias.length()>0) columnName = alias + "." + columnName;
		
		return columnName;
	}	
	
	public static void main(String[] args) {
				
		System.out.println(">>>> "+SQLUtils.nextUID());
		
		System.out.println(boolean.class.getName());
		
		String s1 = "select a.name, b.date, c.id from ClassA a, ClassB b, ClassC c";
		String s2 = "select a.name, b.date, c.id from ClassA a, ClassB b, ClassC c   where  a.id=b.id and b.id=c.id";
		String s3 = "select a.name, b.date, c.id from ClassA a, ClassB b, ClassC as  c   where  a.id=b.id and b.id=c.id and c.id in (select d.id from ClassD d where d.name='ding')";
		String s4 = "from ClassA a order by a.id";
		String s5 = "select a.name from ClassA a order by a.id";

		s3 = buildCriteriaToAll(s3, "validFrom", "<\'2008-6-1\'");
		System.out.println(s3);


		s1 = buildCriteriaToAll(s1, "validFrom", "<\'2008-6-1\'");
		System.out.println(s1);

		s2 = buildCriteriaToAll(s2, "validFrom", "<\'2008-6-1\'");
		System.out.println(s2);

		s4 = buildCriteriaToAll(s4, "validFrom", "<\'2008-6-1\'");
		System.out.println(s4);

		s5 = buildCriteriaToAll(s5, "validFrom", "<\'2008-6-1\'");
		System.out.println(s5);
		
		String s = SQLUtils.convColumnNameToPropertyName("disk_spool_buffer_size_MB");
		System.out.println(s);
	}
}
